﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Xoohoo.Extensions;
using Xoohoo.Models;
using Xoohoo.Models.InputModels;

namespace Xoohoo.Repositories.SqlServerEntLibDAB
{
    /// <summary>
    /// 为了算法稍微清晰，一些可以合并的查询并没有合并，毕竟角色管理并不是很频繁的操作。
    /// </summary>
    public class RoleRepository : RepositoryBase,IRoleRepository
    {
        private readonly Database _db;

        public RoleRepository(string connectionStringName)
        {
            this._db = DatabaseFactory.CreateDatabase(connectionStringName);

        }

        #region IRoleRepository Members

        public Role GetItem(Guid roleID)
        {
            Role role = GetItemByRoleID(roleID);
            if (role == null) return null;
            role.Permissions = GetPermissions(roleID);
            return role;
        }

        public Role GetItem(string roleName)
        {
            string sql = "select TOP 1 * from Role Where RoleName=@RoleName";
            DbCommand cmd = _db.GetSqlStringCommand(sql);
            _db.AddInParameter(cmd, "RoleName", DbType.String, roleName);
            Role role = GetItem(cmd);
            if (role == null) return null;

            role.Permissions = GetPermissions(role.RoleID);
            return role;
        }

        public List<RoleBasic> GetList()
        {
            string sql = "select * from Role Order by DisplayOrder";
            DbCommand cmd = _db.GetSqlStringCommand(sql);
            IDataReader dr = _db.ExecuteReader(cmd);

            return ProjectList(dr);
        }

        public bool Save(RoleInput roleInput)
        {
            string sql;
            DbCommand cmd;
            bool isEdit = false;

            //判断数据库是否
            if (!roleInput.RoleID.IsNullOrEmpty())
            {
                sql = "SELECT CASE WHEN EXISTS (SELECT NULL FROM {0}) THEN 1 ELSE 0 END AS Result";
                cmd = _db.GetSqlStringCommand(sql.FormatWith("Role Where RoleID=@RoleID"));

                _db.AddInParameter(cmd, "RoleID", DbType.Guid, roleInput.RoleID);

                if (Convert.ToBoolean(_db.ExecuteScalar(cmd)))
                    isEdit = true;
                else
                    return false;
            }
            else
            {
                roleInput.RoleID = Guid.NewGuid();
            }

            if (isEdit)
            {
                sql = "Delete UserRoleRelationShip Where RoleID=@RoleID";
                cmd = _db.GetSqlStringCommand(sql);
                _db.AddInParameter(cmd, "RoleID", DbType.Guid, roleInput.RoleID);
                _db.ExecuteNonQuery(cmd);

                sql = "Delete RolePermissionRelationShip Where RoleID=@RoleID";
                cmd = _db.GetSqlStringCommand(sql);
                _db.AddInParameter(cmd, "RoleID", DbType.Guid, roleInput.RoleID);
                _db.ExecuteNonQuery(cmd);

                sql = "Update Role Set RoleName=@RoleName Where RoleID=@RoleID";
                cmd = _db.GetSqlStringCommand(sql);
                _db.AddInParameter(cmd, "RoleName", DbType.String, roleInput.RoleName);
                _db.AddInParameter(cmd, "RoleID", DbType.Guid, roleInput.RoleID);
                _db.ExecuteNonQuery(cmd);
            }
            else
            {
                int displayOrder = GetMaxDisplayOrder() + 1;

                sql = "Insert Into Role(RoleID,RoleName,DisplayOrder,IsSystem) Values "
                    + " (@RoleID,@RoleName,@DisplayOrder,@IsSystem)";
                cmd = _db.GetSqlStringCommand(sql);
                _db.AddInParameter(cmd, "DisplayOrder", DbType.Int32, displayOrder);
                _db.AddInParameter(cmd, "RoleName", DbType.String, roleInput.RoleName);
                _db.AddInParameter(cmd, "RoleID", DbType.Guid, roleInput.RoleID);
                _db.AddInParameter(cmd, "IsSystem", DbType.Boolean, 0);
                _db.ExecuteNonQuery(cmd);
            }

            if (roleInput.Permissions != null)
            {
                sql = "Insert Into RolePermissionRelationship(RoleID,PermissionID) Values "
                    + "(@RoleID,@PermissionID)";
                cmd = _db.GetSqlStringCommand(sql);

                foreach (var per in roleInput.Permissions)
                {
                    cmd.Parameters.Clear();
                    _db.AddInParameter(cmd, "RoleID", DbType.Guid, roleInput.RoleID);
                    _db.AddInParameter(cmd, "PermissionID", DbType.Guid, per);
                    _db.ExecuteNonQuery(cmd);
                }
            }
            return true;
        }

        public bool Remove(Guid roleID)
        {
            Role roleToRemove = GetItemByRoleID(roleID);

            // 保证“系统管理员”不被删除
            if (roleToRemove == null || roleToRemove.IsSystem) return false;

            string sql;
            DbCommand cmd;

            sql = "Update Role Set DisplayOrder=DisplayOrder-1 Where DisplayOrder>@DisplayOrder";
            cmd = _db.GetSqlStringCommand(sql);
            _db.AddInParameter(cmd, "DisplayOrder", DbType.Int32, roleToRemove.DisplayOrder);
            _db.ExecuteNonQuery(cmd);

            sql = "Delete RolePermissionRelationShip Where RoleID=@RoleID";
            cmd = _db.GetSqlStringCommand(sql);
            _db.AddInParameter(cmd, "RoleID", DbType.Guid, roleID);
            _db.ExecuteNonQuery(cmd);

            sql = "Delete UserGroupRoleRelationShip Where RoleID=@RoleID";
            cmd = _db.GetSqlStringCommand(sql);
            _db.AddInParameter(cmd, "RoleID", DbType.Guid, roleID);
            _db.ExecuteNonQuery(cmd);

            sql = "Delete Role Where RoleID=@RoleID";
            cmd = _db.GetSqlStringCommand(sql);
            _db.AddInParameter(cmd, "RoleID", DbType.Guid, roleID);
            _db.ExecuteNonQuery(cmd);

            return true;
        }

        public bool Move(Guid roleID, MovingTarget target)
        {
            string sql;
            DbCommand cmd;

            var roleToMove = GetItemByRoleID(roleID);

            // 保证DisplayOrder为 1 的“系统管理员”不被移动
            if (roleToMove == null || roleToMove.DisplayOrder == 1) return false;

            // 防止DisplayOrder为2的项非法篡改"系统管理员",也就是说DisplayOrder必须大于或等于3
            if (MovingTarget.Up == target)
            {
                if (roleToMove.DisplayOrder < 3) return false;

                sql = "select top 1 RoleID from Role Where DisplayOrder<@DisplayOrder Order by DisplayOrder desc";
                cmd = _db.GetSqlStringCommand(sql);
                _db.AddInParameter(cmd, "DisplayOrder", DbType.Int32, roleToMove.DisplayOrder);
                object o = _db.ExecuteScalar(cmd);
                if (o != null)
                {
                    sql = "Update Role Set DisplayOrder=DisplayOrder+1 Where RoleID=@RoleID";
                    cmd = _db.GetSqlStringCommand(sql);
                    _db.AddInParameter(cmd, "RoleID", DbType.String, o.ToString());
                    _db.ExecuteNonQuery(cmd);

                    sql = "Update Role Set DisplayOrder=DisplayOrder-1 Where RoleID=@RoleID";
                    cmd = _db.GetSqlStringCommand(sql);
                    _db.AddInParameter(cmd, "RoleID", DbType.Guid, roleID);
                    _db.ExecuteNonQuery(cmd);
                }
            }
            else if (MovingTarget.Down == target)
            {
                sql = "select top 1 RoleID from Role Where DisplayOrder>@DisplayOrder Order by DisplayOrder";
                cmd = _db.GetSqlStringCommand(sql);
                _db.AddInParameter(cmd, "DisplayOrder", DbType.Int32, roleToMove.DisplayOrder);
                object o = _db.ExecuteScalar(cmd);
                if (o != null)
                {
                    sql = "Update Role Set DisplayOrder=DisplayOrder-1 Where RoleID=@RoleID";
                    cmd = _db.GetSqlStringCommand(sql);
                    _db.AddInParameter(cmd, "RoleID", DbType.String, o.ToString());
                    _db.ExecuteNonQuery(cmd);

                    sql = "Update Role Set DisplayOrder=DisplayOrder+1 Where RoleID=@RoleID";
                    cmd = _db.GetSqlStringCommand(sql);
                    _db.AddInParameter(cmd, "RoleID", DbType.Guid, roleID);
                    _db.ExecuteNonQuery(cmd);
                }
            }
            else
                return false;

            return true;
        }

        #endregion

        #region Private Methods

        private Role GetItem(DbCommand cmd)
        {
            IDataReader dr = _db.ExecuteReader(cmd);
            Role item = ProjectBeforeRead(dr);
            dr.Close();
            return item;
        }

        private Role GetItemByRoleID(Guid roleID)
        {
            string sql = "select r.RoleID,r.RoleName,r.DisplayOrder from Role r Where RoleID=@RoleID";
            DbCommand cmd = _db.GetSqlStringCommand(sql);
            _db.AddInParameter(cmd, "RoleID", DbType.Guid, roleID);
            return GetItem(cmd);
        }

        private List<RoleBasic> ProjectList(IDataReader dr)
        {
            List<RoleBasic> roles = new List<RoleBasic>();

            if (dr == null) return roles;

            while (dr.Read())
            {
                roles.Add(dr.ToModel<RoleBasic>());
            }
            return roles;
        }

        private Role ProjectBeforeRead(IDataReader dr)
        {
            if (dr == null || !dr.Read()) return null;

            return Project(dr);
        }

        private Role Project(IDataReader dr)
        {
            if (dr == null) return null;

            Role role = dr.ToModel<Role>();

            return role;
        }

        private int GetMaxDisplayOrder()
        {
            string sql = "Select Max(DisplayOrder) From Role";
            DbCommand cmd = _db.GetSqlStringCommand(sql);
            int? result = _db.ExecuteScalarInt(cmd);
            return result.HasValue ? result.Value : 0;
        }

        private List<PermissionBasic> GetPermissions(Guid roleID)
        {
            string sql = "Select p.PermissionID,p.ModuleName,p.PermissionName From Permission p"
        + " Inner Join Rolepermissionrelationship rp On p.PermissionID=rp.PermissionID"
        + " Inner Join Role r On rp.RoleID=r.RoleID"
        + " Where r.RoleID=@RoleID";
            DbCommand cmd = _db.GetSqlStringCommand(sql);
            _db.AddInParameter(cmd, "RoleID", DbType.Guid, roleID);
            IDataReader dr = _db.ExecuteReader(cmd);

            List<PermissionBasic> pers = new List<PermissionBasic>();

            while (dr.Read())
            {
                pers.Add(dr.ToModel<PermissionBasic>());
            }
            dr.Close();

            return pers;

        }

        #endregion

    }
}
